﻿using Mysoft.Map.Extensions.DAL;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;

namespace Mysoft.QuickCode.DbExpressionExtensions
{
    public static class EntityTableExtensions
    {

        /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityTable"></param>
        /// <param name="list"></param>
        public static void BatchInsert<T>(this EntityTable<T> entityTable, List<T> entityList)
            where T : class
        {
            PropertyInfo[] props = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public);

            //创建DataTable，准备写入临时表
            DataTable table = new DataTable();
            foreach (KeyValuePair<string, string> kv in entityTable.PropertyToFileds)
            {
                DataColumn column = new DataColumn(kv.Value);
                PropertyInfo p = props.FirstOrDefault(m => m.Name == kv.Key);
                if (p.PropertyType.IsGenericType && p.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                {
                    //dataset不支持nullable<>数据类型
                    column.DataType = p.PropertyType.GetGenericArguments()[0];
                    column.AllowDBNull = true;
                }
                else
                {
                    column.DataType = p.PropertyType;
                }
                table.Columns.Add(column);  //创建一个列名，用于数据表字段
            }
            foreach (var e in entityList)
            {
                DataRow row = table.NewRow();
                foreach (KeyValuePair<string, string> kv in entityTable.PropertyToFileds)
                {
                    //根据字段名称查找当前对象对应属性值
                    object v = props.FirstOrDefault(m => m.Name == kv.Key).GetValue(e, null);
                    if (v == null && row.Table.Columns[kv.Value].AllowDBNull)
                    {
                        row[kv.Value] = DBNull.Value;
                    }
                    else
                    {
                        row[kv.Value] = v;
                    }
                }
                //赋值完成后，将当前行数据写入到table中
                table.Rows.Add(row);
            }

            //DataTable使用SqlBulkCopy批量写入数据库 
            using (ConnectionScope scope = new ConnectionScope())
            {
                //scope创建SqlBulkCopy，不需要启用事务。
                SqlBulkCopy bulkCopy = scope.CreateSqlBulkCopy(SqlBulkCopyOptions.Default);
                bulkCopy.DestinationTableName = entityTable.GetTableName();
                bulkCopy.WriteToServer(table); //写入
                bulkCopy.Close();
            }
        }

        /// <summary>
        /// Select * 查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityTable"></param>
        /// <returns></returns>
        public static EntityTable<T> Select<T>(this EntityTable<T> entityTable)
            where T : class
        {
            entityTable.CommandText.Insert(0, string.Format("SELECT * FROM {0} ", entityTable.GetTableName()));
            return entityTable;
        }

        /// <summary>
        /// Select {字段}
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityTable"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        public static EntityTable<T> Select<T>(this EntityTable<T> entityTable, Expression<Func<T, object[]>> fields)
            where T : class
        {
            var memberExpr = (NewArrayExpression)fields.Body;
            List<string> paraList = new List<string>();

            //由于将字段添加到object[]数组后，.NET内部会将类型转换加上Convert方法，因此需要区别处理。
            foreach (Expression exp in memberExpr.Expressions)
            {
                if (exp is MemberExpression)
                {
                    //MemberExpression类型直接获取字段名称
                    paraList.Add(string.Format("[{0}]", entityTable.PropertyToFileds[(exp as MemberExpression).Member.Name]));
                }
                else if (exp is UnaryExpression)
                {
                    //有Convert方法的则需要从UnaryExpression中再解析MemberExpression
                    paraList.Add(string.Format("[{0}]", entityTable.PropertyToFileds[((exp as UnaryExpression).Operand as MemberExpression).Member.Name]));
                }
                else
                {
                    throw new NotImplementedException("不支持的表达式类型：" + exp.ToString());
                }
            }

            //生成SQL查询部分语句
            entityTable.CommandText.Insert(0, string.Format("SELECT {0} FROM {1} ", string.Join(", ", paraList.ToArray()), entityTable.GetTableName()));
            return entityTable;
        }

        /// <summary>
        /// 更新数据库对象字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityTable"></param>
        /// <param name="expUpdate">要更新的字段</param>
        /// <returns></returns>
        public static EntityTable<T> Update<T>(this EntityTable<T> entityTable, Expression<Func<T, T>> expUpdate)
            where T : class
        {
            var updateMemberExpr = (MemberInitExpression)expUpdate.Body;
            List<string> fields = new List<string>();

            //拼接更新字段
            foreach (MemberAssignment item in updateMemberExpr.Bindings.Cast<MemberAssignment>().ToList())
            {
                UpdatePartialEvaluator<T> pe = new UpdatePartialEvaluator<T>(entityTable);
                Expression unknowExp = pe.Eval(item.Expression);
                ConstantExpression ce = unknowExp as ConstantExpression;
                if (ce == null)
                {
                    //不可直接转换为值对象的表达式
                    fields.Add(string.Format("[{0}]={1}", entityTable.PropertyToFileds[item.Member.Name], unknowExp.ToString())); //设置更新字段
                    //添加参数到entityTable对象的参数集合中
                    foreach (KeyValuePair<string, object> p in pe.GetParameters().Reverse())
                    {
                        entityTable.Parameters.Push(p);
                    }
                }
                else
                {
                    //可直接转换为值对象的表达式
                    string para = string.Format("p{0}", entityTable.Parameters.Count);   //将参数按序号生成，防止重复
                    fields.Add(string.Format("[{0}]=@{1}", entityTable.PropertyToFileds[item.Member.Name], para)); //设置更新字段
                    //添加参数到entityTable对象的参数集合中
                    entityTable.Parameters.Push(new KeyValuePair<string, object>(para, ce.Value));
                }
            }

            //格式化更新语句Update Table Set Fields部分
            entityTable.CommandText.Append(string.Format("UPDATE {0} SET {1} ", entityTable.GetTableName(), string.Join(", ", fields.ToArray())));

            return entityTable;
        }

        /// <summary>
        /// 删除对象对应的数据表中的数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityTable"></param>
        /// <returns></returns>
        public static EntityTable<T> Delete<T>(this EntityTable<T> entityTable)
            where T : class
        {
            //构建删除语句前半部分
            entityTable.CommandText.Append(string.Format("DELETE FROM {0} ", entityTable.GetTableName()));
            return entityTable;
        }


        /// <summary>
        /// Where条件
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="expWhere">where后的条件语句</param>
        /// <returns></returns>
        public static EntityTable<T> Where<T>(this EntityTable<T> entity, Expression<Func<T, bool>> expWhere)
            where T : class
        {
            if (expWhere != null)
            {
                EntityTableConditionBuilder<T> conditionBuilder = new EntityTableConditionBuilder<T>(entity);
                //获取Where语句
                conditionBuilder.Build(expWhere);
                string whereCondition = conditionBuilder.Condition;

                entity.CommandText.Append(string.Format("WHERE {0} ", whereCondition));
            }
            return entity;
        }

        /// <summary>
        /// Top 记录条数
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entity"></param>
        /// <param name="top">记录条数</param>
        /// <returns></returns>
        public static EntityTable<T> Top<T>(this EntityTable<T> entity, int top)
            where T : class
        {
            //在拼接的SQL语句中加入TOP N。将原来的SELECT 替换为SELECT TOP N。
            string sql = string.Empty;
            if (Regex.Match(entity.CommandText.ToString(), "^SELECT DISTINCT ", RegexOptions.IgnoreCase).Success)
            {
                sql = Regex.Replace(entity.CommandText.ToString().Trim(), "^SELECT DISTINCT ",
                    string.Format("SELECT DISTINCT TOP {0} ", top), RegexOptions.IgnoreCase);
            }
            else
            {
                sql = Regex.Replace(entity.CommandText.ToString(), "^SELECT ",
                    string.Format("SELECT TOP {0} ", top), RegexOptions.IgnoreCase);
            }
            entity.CommandText = new StringBuilder(sql);
            return entity;
        }

        /// <summary>
        /// Asc排序
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityTable"></param>
        /// <param name="fields"></param>
        /// <param name="order">排序方式</param>
        /// <returns></returns>
        public static EntityTable<T> OrderBy<T>(this EntityTable<T> entityTable, Expression<Func<T, object[]>> fields, OrderByType order)
            where T : class
        {
            var memberExpr = (NewArrayExpression)fields.Body;
            List<string> paraList = new List<string>();

            //由于将字段添加到object[]数组后，.NET内部会将类型转换加上Convert方法，因此需要区别处理。
            foreach (Expression exp in memberExpr.Expressions)
            {
                if (exp is MemberExpression)
                {
                    //MemberExpression类型直接获取字段名称
                    paraList.Add(string.Format("[{0}]", entityTable.PropertyToFileds[(exp as MemberExpression).Member.Name]));
                }
                else if (exp is UnaryExpression)
                {
                    //有Convert方法的则需要从UnaryExpression中再解析MemberExpression
                    paraList.Add(string.Format("[{0}]", entityTable.PropertyToFileds[((exp as UnaryExpression).Operand as MemberExpression).Member.Name]));
                }
                else
                {
                    throw new NotImplementedException("不支持的表达式类型：" + exp.ToString());
                }
            }

            //生成SQL查询部分语句
            if (paraList.Count > 0)
            {
                string arrParam = string.Join(", ", paraList.ToArray());
                paraList.Clear();
                paraList.Add(arrParam);
                paraList.Add(order == OrderByType.ASC ? "ASC" : "DESC");

                if (!entityTable.Ordered)
                {
                    entityTable.CommandText.AppendFormat("ORDER BY ");
                    entityTable.CommandText.AppendFormat("{0} {1} ", paraList.ToArray());
                }
                else
                {
                    entityTable.CommandText.AppendFormat(",{0} {1} ", paraList.ToArray());
                }

                //标记为已经排序过
                entityTable.Ordered = true;
            }
            return entityTable;
        }

        /// <summary>
        /// 会在Select后加Distinct，如果已经存在，则不会加入
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityTable"></param>
        /// <returns></returns>
        public static EntityTable<T> Distinct<T>(this EntityTable<T> entityTable)
            where T : class
        {
            string sql = string.Empty;
            //在拼接的SQL语句中加入TOP N。将原来的SELECT 替换为SELECT DISTINCT。
            if (!Regex.Match(entityTable.CommandText.ToString(), "^SELECT DISTINCT ", RegexOptions.IgnoreCase).Success)
            {
                sql = Regex.Replace(entityTable.CommandText.ToString(), "^SELECT ", string.Format("SELECT DISTINCT "), RegexOptions.IgnoreCase);
            }
            entityTable.CommandText = new StringBuilder(sql);
            return entityTable;
        }
        
        /// <summary>
        /// 解析字段列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="entityTable"></param>
        /// <param name="fields"></param>
        /// <returns></returns>
        internal static List<string> ParseFieldList<T>(EntityTable<T> entityTable, Expression<Func<T, object[]>> fields)
            where T : class
        {
            var memberExpr = (NewArrayExpression)fields.Body;
            List<string> paraList = new List<string>();

            //由于将字段添加到object[]数组后，.NET内部会将类型转换加上Convert方法，因此需要区别处理。
            foreach (Expression exp in memberExpr.Expressions)
            {
                if (exp is MemberExpression)
                {
                    //MemberExpression类型直接获取字段名称
                    paraList.Add(string.Format("[{0}]", entityTable.PropertyToFileds[(exp as MemberExpression).Member.Name]));
                }
                else if (exp is UnaryExpression)
                {
                    //有Convert方法的则需要从UnaryExpression中再解析MemberExpression
                    paraList.Add(string.Format("[{0}]", entityTable.PropertyToFileds[((exp as UnaryExpression).Operand as MemberExpression).Member.Name]));
                }
                else
                {
                    throw new NotImplementedException("不支持的表达式类型：" + exp.ToString());
                }
            }
            return paraList;
        }

        //public static EntityTable<T> Join<T, M>(this EntityTable<T> entityTable, EntityTable<M> joinEntityTable, Expression<Func<T, M, bool>> linkConditions)
        //    where T : class
        //    where M : class
        //{
        //    JoinConditionBuilder<T, M> conditionBuilder = new JoinConditionBuilder<T, M>(entityTable, joinEntityTable);
        //    //获取Where语句
        //    conditionBuilder.Build(linkConditions);
        //    string condition = conditionBuilder.Condition;

        //    entityTable.CommandText.AppendFormat("INNER JOIN {0} ON {1} ", entityTable.GetTableName(), condition);
        //    return entityTable;
        //}
    }
}
